package com.murong.ai.coder.domain.database.sqlhandler;

import com.murong.ai.coder.entity.TableEntity;
import com.murong.ai.coder.infrastructure.util.MrStringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class MySqlHandler {
    /**
     * 支持多条SQL串（建表+注释+索引）
     */
    public static TableEntity handlerCreateTable(String sqlAll) {
        TableEntity entity = new TableEntity();
        // 1. 表名
        Pattern tableNamePattern = Pattern.compile("create table [`]?([a-zA-Z0-9_]+)[`]?", Pattern.CASE_INSENSITIVE);
        Matcher tableNameMatcher = tableNamePattern.matcher(sqlAll);
        if (tableNameMatcher.find()) {
            String tableName = tableNameMatcher.group(1);
            entity.setTableNameSnake(tableName);
            String tableNameCamel=tableName;
            if (tableNameCamel.startsWith("T_")) {
                tableNameCamel=tableNameCamel.substring(2);
            }
            tableNameCamel=MrStringUtils.toCamel(tableNameCamel);
            tableNameCamel=MrStringUtils.toFirstLetter(tableNameCamel);
            entity.setTableNameCamel(tableNameCamel);
        }
        // 2. 字段
        Pattern fieldPattern = Pattern.compile("create table [`]?[a-zA-Z0-9_]+[`]?\\s*\\((.*?)\\)\\s*(ENGINE|COMMENT|;|$)", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        Matcher fieldMatcher = fieldPattern.matcher(sqlAll);
        List<TableEntity.Field> fields = new ArrayList<>();
        if (fieldMatcher.find()) {
            String fieldsBlock = fieldMatcher.group(1);
            String[] lines = fieldsBlock.split(",\n|,\r\n|,\r");
            for (String line : lines) {
                line = line.trim();
                if (line.toLowerCase().startsWith("primary key") || line.toLowerCase().startsWith("unique key") || line.toLowerCase().startsWith("key") || line.toLowerCase().startsWith("constraint")) {
                    continue;
                }
                // 匹配字段名、类型、长度
                Pattern colPattern = Pattern.compile("[`]?([a-zA-Z0-9_]+)[`]?\\s+([a-zA-Z0-9_]+)(\\(([^)]+)\\))?", Pattern.CASE_INSENSITIVE);
                Matcher colMatcher = colPattern.matcher(line);
                if (colMatcher.find()) {
                    String name = colMatcher.group(1);
                    String dbType = colMatcher.group(2);
                    String length = colMatcher.group(4);
                    TableEntity.Field field = new TableEntity.Field();
                    field.setNameSnake(name);
                    field.setNameCamel(MrStringUtils.toCamel(name));
                    field.setDbTyp(dbType); // 存数据库类型
                    field.setType(toJavaType(dbType)); // 存Java类型
                    if (length != null) {
                        String[] lenArr = length.split(",");
                        try { field.setLength(Integer.parseInt(lenArr[0].trim())); } catch (Exception ignore) {}
                        // 可扩展：小数位
                    }
                    // 字段注释（如有）
                    Pattern commentPattern = Pattern.compile("comment ['\"]([^'\"]*)['\"]", Pattern.CASE_INSENSITIVE);
                    Matcher commentMatcher = commentPattern.matcher(line);
                    if (commentMatcher.find()) {
                        String comment = commentMatcher.group(1);
                        String[] parts = comment.split("\\|");
                        field.setCommentCn(parts[0]);
                        if (parts.length > 1) field.setCommentEn(parts[1]);
                    }
                    fields.add(field);
                }
            }
        }
        // 3. 解析comment on column ... is ...
        Map<String, String> commentMap = new HashMap<>();
        Pattern commentPattern = Pattern.compile("comment on column ([A-Z0-9_]+)\\.([A-Z0-9_]+) is '([^']*)'", Pattern.CASE_INSENSITIVE);
        Matcher commentMatcher = commentPattern.matcher(sqlAll);
        while (commentMatcher.find()) {
            String col = commentMatcher.group(2);
            String comment = commentMatcher.group(3);
            commentMap.put(col, comment);
        }
        for (TableEntity.Field f : fields) {
            if (commentMap.containsKey(f.getNameSnake())) {
                String comment = commentMap.get(f.getNameSnake());
                String[] parts = comment.split("\\|");
                f.setCommentCn(parts[0]);
                if (parts.length > 1) f.setCommentEn(parts[1]);
            }
        }
        entity.setFields(fields);
        // 4. 主键
        Pattern pkPattern = Pattern.compile("primary key \\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher pkMatcher = pkPattern.matcher(sqlAll);
        if (pkMatcher.find()) {
            TableEntity.Index pk = new TableEntity.Index();
            String tableName = entity.getTableNameSnake();
            pk.setName("PK_" + tableName);
            pk.setType("PRIMARY");
            String[] pkFields = pkMatcher.group(1).replace("`", "").split(",");
            List<String> pkList = new ArrayList<>();
            for (String pkf : pkFields) pkList.add(pkf.trim());
            pk.setFields(pkList);
            entity.setPrimaryKey(pk);
        }

        // 5. 索引（支持建表内KEY/UNIQUE KEY/INDEX定义）
        // 先解析建表语句内的索引
        List<TableEntity.Index> indexes = new ArrayList<>();
        if (fieldMatcher.find()) {
            String fieldsBlock = fieldMatcher.group(1);
            String[] lines = fieldsBlock.split(",\n|,\r\n|,\r");
            for (String line : lines) {
                line = line.trim();
                // 普通索引
                Pattern keyPattern = Pattern.compile("key [`]?([a-zA-Z0-9_]+)[`]? \\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
                Matcher keyMatcher = keyPattern.matcher(line);
                if (keyMatcher.find()) {
                    TableEntity.Index idx = new TableEntity.Index();
                    idx.setName(keyMatcher.group(1));
                    idx.setType("INDEX");
                    String[] idxFields = keyMatcher.group(2).replace("`", "").split(",");
                    List<String> idxList = new ArrayList<>();
                    for (String f : idxFields) idxList.add(f.trim());
                    idx.setFields(idxList);
                    indexes.add(idx);
                    continue;
                }
                // 唯一索引
                Pattern uniqueKeyPattern = Pattern.compile("unique key [`]?([a-zA-Z0-9_]+)[`]? \\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
                Matcher uniqueKeyMatcher = uniqueKeyPattern.matcher(line);
                if (uniqueKeyMatcher.find()) {
                    TableEntity.Index idx = new TableEntity.Index();
                    idx.setName(uniqueKeyMatcher.group(1));
                    idx.setType("UNIQUE");
                    String[] idxFields = uniqueKeyMatcher.group(2).replace("`", "").split(",");
                    List<String> idxList = new ArrayList<>();
                    for (String f : idxFields) idxList.add(f.trim());
                    idx.setFields(idxList);
                    indexes.add(idx);
                    continue;
                }
            }
        }
        // 继续解析建表外的CREATE INDEX语句（支持反引号）
        Pattern idxPattern = Pattern.compile(
            "create\\s+(unique\\s+)?index\\s+[`]?([a-zA-Z0-9_]+)[`]?\\s+on\\s+[`]?([a-zA-Z0-9_]+)[`]?\\s*\\(([^)]+)\\)",
            Pattern.CASE_INSENSITIVE);
        Matcher idxMatcher = idxPattern.matcher(sqlAll);
        while (idxMatcher.find()) {
            TableEntity.Index idx = new TableEntity.Index();
            idx.setName(idxMatcher.group(2));
            idx.setType(idxMatcher.group(1) != null ? "UNIQUE" : "INDEX");
            String[] idxFields = idxMatcher.group(4).replace("`", "").split(",");
            List<String> idxList = new ArrayList<>();
            for (String f : idxFields) idxList.add(f.trim());
            idx.setFields(idxList);
            indexes.add(idx);
        }
        entity.setIndexes(indexes);
        // 6. 表注释
        Pattern tableCommentPattern = Pattern.compile("comment=['\"]([^'\"]*)['\"]", Pattern.CASE_INSENSITIVE);
        Matcher tableCommentMatcher = tableCommentPattern.matcher(sqlAll);
        if (tableCommentMatcher.find()) {
            String comment = tableCommentMatcher.group(1);
            String[] parts = comment.split("\\|");
            entity.setTableCommentCn(parts[0]);
            if (parts.length > 1) entity.setTableCommentEn(parts[1]);
        } else {
            // 兼容：如无表注释，默认用表名
            entity.setTableCommentCn(entity.getTableNameSnake() + "表");
        }
        return entity;
    }

    private static String toJavaType(String dbType) {
        dbType = dbType.toUpperCase();
        switch (dbType) {
            case "VARCHAR":
            case "VARCHAR2":
            case "CHAR":
            case "TEXT":
                return "String";
            case "NUMBER":
            case "DECIMAL":
            case "NUMERIC":
                return "YGAmt";
            case "INT":
            case "INTEGER":
            case "SMALLINT":
                return "Integer";
            case "BIGINT":
                return "Long";
            case "DATE":
            case "DATETIME":
            case "TIMESTAMP":
                return "Date";
            case "FLOAT":
            case "DOUBLE":
                return "Double";
            case "BIT":
            case "BOOLEAN":
                return "Boolean";
            default:
                return "String";
        }
    }
}
